﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmhouse : Form
    {
        public frmhouse()
        {
            InitializeComponent();
        }

        private void frmhouse_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            showType();
        }

        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType;
            sqlType = "select * from osm";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType, Conn);
            da.Fill(ds, "osm");

            comboBox1.DataSource = ds.Tables["osm"];
            comboBox1.ValueMember = "OSMID";
            comboBox1.DisplayMember = "OSMNAM";

        }
       

        private void button3_Click(object sender, EventArgs e)
        {
            Close();

        }
        private void showdata1()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql1 = "select * from house where HOUSEID = '" + houseid.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataReader reader = cmd1.ExecuteReader();
            if (houseid.Text == "")
            {
                MessageBox.Show("กรุณาป้อนคำที่ต้องการค้นหา !!", "แจ้งเตือน");
                return;
            }
            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    
                    houseid.Text =  reader["HOUSEID"].ToString();
                    addr.Text = reader["ADDRESS"].ToString();
                    nfamily.Text = reader["NFAMILY"].ToString();
                    comboBox1.Text = reader["OSMID"].ToString();
                    cmbhou.Text = reader["HCARE"].ToString();
                    cbmwat.Text = reader["WATER"].ToString();
                    cmbtwat.Text = reader["WATTYPE"].ToString();
                    cmbtoi.Text = reader["TOILET"].ToString();
                    cmbtbin.Text = reader["GARBAGE"].ToString();
                    cmbwater.Text = reader["WATERTM"].ToString();
                    cmbkang.Text = reader["DUPABLE"].ToString();
                    cmblight.Text = reader["LIGHT"].ToString();
                    cmbclean.Text = reader["CLEAN"].ToString();
                    cmbra.Text = reader["VENTIA"].ToString();
                    cmbmfood.Text = reader["MFOOD"].ToString();
                    cmbbctrl.Text = reader["BCTRL"].ToString();
                    cmbactrl.Text = reader["ACTRL"].ToString();
                    dateup.Text = reader["d_UPDATE"].ToString();


                }

                reader.Close();

            }


        }
        private void button1_Click(object sender, EventArgs e)
        {

            if (houseid.Text == "")
            {
                MessageBox.Show("กรุณากรอกรายชื่อต้องการค้นหา !!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                showdata1();
            }
        }

        private void button11_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            
            string strdate = Convert.ToString(dateup.Value.Year) + "/" + Convert.ToString(dateup.Value.Month) + "/" + Convert.ToString(dateup.Value.Day);//วันที่

            if (houseid.Text != "" && nfamily.Text != "" && comboBox1.Text != "" && cmbhou.Text != "" && cbmwat.Text != "" && cmbtwat.Text != "" && cmbtoi.Text != "" && cmbtbin.Text != "" && cmbwater.Text != "" && cmbkang.Text != "" && cmblight.Text != "" && cmbclean.Text != "" && cmbra.Text != "" && cmbmfood.Text != "" && cmbbctrl.Text != "" && cmbactrl.Text != "" && strdate != "")
            {

                string strOut = "";
                strOut += "เลขที่รหัสบ้าน :" + houseid.Text + "\n";
                strOut += "ที่อยู่ :" + addr.Text + "\n";
                strOut += "จำนวนครอบครัว :" + nfamily.Text + "\n";
                strOut += "อสม.ผู้รับผิดชอบ :" + comboBox1.SelectedValue + "\n";
                strOut += "การจัดบ้านถูกหลัก :" + cmbhou.Text + "\n";
                strOut += "น้ำดื่มสะอาดเพียงพอ :" + cmbtwat.Text + "\n";
                strOut += "ประเภทน้ำดื่ม :" + cmbtoi.Text + "\n";
                strOut += "การมีส้วม :" + cmbtbin.Text + "\n";
                strOut += "วิธีกำจัดขยะ :" + cmbwater.SelectedValue + "\n";
                strOut += "การจัดน้ำเสีย :" + cmbkang.Text + "\n";
                strOut += "ความคงทน :" + cmblight.Text + "\n";
                strOut += "ความสะอาด :" + cmbclean.Text + "\n";
                strOut += "การระบาย :" + cmbra.Text + "\n";
                strOut += "สารปรุงแต่ง :" + cmbmfood.SelectedValue + "\n";
                strOut += "การควบคุมสัตว์นำโรค :" + cmbbctrl.Text + "\n";
                strOut += "การควบคุมแมลงนำโรค :" + cmbactrl.Text + "\n";
                strOut += "วันที่ปรับปรุง :" + dateup.Text + "\n";


                MessageBox.Show(strOut, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);

                string query = "insert into house values('" + houseid.Text + "','"+addr.Text+"','" + nfamily.Text + "','" + comboBox1.SelectedValue + "','" + cmbhou.Text + "','" + cbmwat.Text + "','" + cmbtwat.Text + "','" + cmbtoi.Text + "','" + cmbtbin.Text + "','" + cmbwater.Text + "','" + cmbkang.Text + "','" + cmblight.Text + "','" + cmbclean.Text + "','" + cmbra.Text + "','" + cmbmfood.Text + "','" + cmbbctrl.Text + "','" + cmbactrl.Text + "','" + strdate + "')";

                SqlCommand cmd = new SqlCommand(query, Conn);

                //Use ExecuteNonQuery to insert data.   
                cmd.ExecuteNonQuery();


                string sql1 = "SELECT HOUSEID,ADDRESS,NFAMILY,OSMNAM,HCARE,WATER,WATTYPE,TOILET,GARBAGE,WATERTM,DUPABLE,LIGHT,CLEAN,VENTIA,MFOOD,BCTRL,ACTRL,d_UPDATE FROM house,osm WHERE  house.OSMID=osm.OSMID and ADDRESS ='" + houseid.Text + "') ";
                SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                DataSet ds = new DataSet();
                 MessageBox.Show("ข้อมูลถูกบันทึกเรียบร้อยแล้ว");
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
           
        }

        private void button2_Click(object sender, EventArgs e)
        {
            cleardata();
        }
        private void cleardata()
        {
            houseid .Text="";
            addr.Text = "";
            nfamily.Text="";
            comboBox1.Text="";
            cmbhou.Text="";
            cbmwat.Text="";
            cmbtwat.Text="";
            cmbtoi.Text="";
            cmbtbin.Text="";
            cmbwater.Text="";
            cmbkang.Text="";
            cmblight.Text="";
            cmbclean.Text="";
            cmbra.Text="";
            cmbmfood.Text="";
            cmbbctrl.Text="";
            cmbactrl.Text="";
            dateup .Text="";
        }

        private void button4_Click(object sender, EventArgs e)
        {

        }
        }
    }
